package com.example.mapper;

import com.example.pojo.Bulletins;
import com.example.pojo.Likes;
import com.example.pojo.Posts;
import com.example.pojo.Users;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author ${author}
 * @since 2024-06-01
 */
public interface UsersMapper extends BaseMapper<Users> {

    @Select("SELECT\n" +
            "            u.*,\n" +
            "            d.age,\n" +
            "            d.ipaddress,\n" +
            "            d.experience,\n" +
            "            d.coins,\n" +
            "            e.level,\n" +
            "            e.levelname\n" +
            "        FROM\n" +
            "            users u\n" +
            "        JOIN\n" +
            "            user_details d ON u.uid = d.uid\n" +
            "        JOIN\n" +
            "            ed e ON d.experience >= e.ex\n" +
            "        AND\n" +
            "            NOT EXISTS (\n" +
            "                SELECT 1\n" +
            "                FROM ed e2\n" +
            "                WHERE d.experience >= e2.ex AND e2.ex > e.ex\n" +
            "            )\n" +
            "\t\t\t\tWHERE\n" +
            "\t\t\t\t\t1=1\n" +
            "\t\t\t\t\t\t\tAND u.status =0 ORDER BY u.uid")
    List<Users> getAllUsers();

    @Select("\n" +
            "SELECT\n" +
            "            u.*,\n" +
            "            d.age,\n" +
            "            d.ipaddress,\n" +
            "            d.experience,\n" +
            "            d.coins,\n" +
            "            e.level,\n" +
            "            e.levelname\n" +
            "        FROM\n" +
            "            users u\n" +
            "        JOIN\n" +
            "            user_details d ON u.uid = d.uid\n" +
            "        JOIN\n" +
            "            ed e ON d.experience >= e.ex\n" +
            "        AND\n" +
            "            NOT EXISTS (\n" +
            "                SELECT 1\n" +
            "                FROM ed e2\n" +
            "                WHERE d.experience >= e2.ex AND e2.ex > e.ex\n" +
            "            )\n" +
            "\t\t\t\tWHERE\n" +
            "\t\t\t\t\t1=1\n" +
            "\t\t\t\t\t\t\tAND ${select} LIKE concat('%', #{input}, '%') and u.status = 0 \n" + // 将#{select}修改为${select}
            "\t\t\t\t\t\t\t\t\n" +
            "\t\t\t\t\t\t\tAND NOT EXISTS (\n" +
            "\t\t\t\t\t\tSELECT 1 \n" +
            "\t\t\t\t\t\tFROM ed e2 \n" +
            "\t\t\t\t\t\tWHERE d.experience >= e2.ex \n" +
            "\t\t\t\t\t\tAND e2.ex > e.ex\n" +
            "\t\t\t\t);\n")
    List<Users> getFindUsers(@Param("select") String select, @Param("input") String input);

    @Update("update users set status=#{status} where uid =#{uid}")
    void StatusChange(int status, int uid);

//    @Delete("DELETE FROM users WHERE uid IN (:uids)")
//    int deleteByIds(@Param("uids") List<Long> uids);

    @Select("select * from users where name=#{name} and password = #{password} ")
    Users Login(@RequestParam("name") String name, @RequestParam("password") String password);

    @Insert("insert into users (name,phone,password,image,gender,rdata,status) values (#{name},#{phone},#{password},#{image},#{gender},#{rdata},0)")
    void Register(@RequestParam("name") String name, @RequestParam("phone") String phone, @RequestParam("password") String password, String image, String gender, String rdata);

    @Insert("insert into user_details (uid,age,ipaddress,experience,coins) values (#{uid},22,'湖南郴州',0,0)")
    void Register2(@RequestParam("uid") int uid);

    @Select("select u.*,d.*,e.level,e.levelname from users u join user_details d on u.uid = d.uid join ed  e ON d.experience >= e.ex\n" +
            "        AND\n" +
            "            NOT EXISTS (\n" +
            "                SELECT 1\n" +
            "                FROM ed e2\n" +
            "                WHERE d.experience >= e2.ex AND e2.ex > e.ex\n" +
            "            )\n" +
            "\n" +
            " where name = #{name} and password = #{password}")
    Users findByName_pwd(@RequestParam("name") String name, @RequestParam("password") String password);

    @Select("select u.*,d.*,e.level,e.levelname from users u join user_details d on u.uid = d.uid join ed  e ON d.experience >= e.ex\n" +
            "        AND\n" +
            "            NOT EXISTS (\n" +
            "                SELECT 1\n" +
            "                FROM ed e2\n" +
            "                WHERE d.experience >= e2.ex AND e2.ex > e.ex\n" +
            "            ) where u.uid = #{uid} ")
    Users findByUid(@RequestParam("uid") Integer uid);

    @Select("select u.* from apply a join users u on a.uid = u.uid where bid =#{bid} and a.status =1")
    List<Users> findApply(@RequestParam("bid") Integer bid);

    @Select("select p.*,u.name,u.image,d.age,d.experience,d.ipaddress,d.coins,e.level,e.levelname,(select count(*) from follow WHERE uid = u.uid) AS fc,(SELECT count(*) FROM posts WHERE uid = p.uid) AS pc,(SELECT count(*) FROM attention WHERE otherid = p.uid) AS ac from posts p join users u on p.uid = u.uid join user_details d on u.uid = d.uid join  ed e ON \n" +
            " d.experience >= e.ex\n" +
            "        AND\n" +
            "            NOT EXISTS (\n" +
            "                SELECT 1\n" +
            "                FROM ed e2\n" +
            "                WHERE d.experience >= e2.ex AND e2.ex > e.ex\n" +
            "            )\n" +
            "\t\t\t\t\t\twhere p.uid = #{uid} ORDER BY tdate DESC")
    List<Posts> findInformation(@RequestParam("uid") Integer uid);

    @Select("SELECT *, 'u' AS source\n" +
            "FROM users\n" +
            "WHERE uid IN (\n" +
            "  SELECT COALESCE(otherid, uid)\n" +
            "  FROM attention\n" +
            "  WHERE uid = #{uid}\n" +
            ")\n" +
            "UNION ALL\n" +
            "\n" +
            "SELECT *, 'o' AS source\n" +
            "FROM users\n" +
            "WHERE uid IN (\n" +
            "  SELECT COALESCE(uid, otherid)\n" +
            "  FROM attention\n" +
            "  WHERE otherid = #{uid}\n" +
            ")")
    List<Users> findAttention(@RequestParam("uid") Integer uid);

    @Select("select * from attention where uid = #{uid} and otherid = #{otherid}")
    boolean gz(@RequestParam("uid") Integer uid, @RequestParam("otherid") Integer otherid);

    @Insert("insert  into  attention (uid,otherid) values (#{uid},#{otherid})")
    boolean addGz(@RequestParam("uid") Integer uid, @RequestParam("otherid") Integer otherid);

    @Delete("delete from attention where uid = #{uid} and otherid = #{otherid}")
    boolean delGz(@RequestParam("uid") Integer uid, @RequestParam("otherid") Integer otherid);

    @Select("select * from follow where uid = #{uid} and bid = #{bid}")
    boolean gzbar(@RequestParam("uid") Integer uid, @RequestParam("bid") Integer bid);

    @Insert("insert  into  follow (uid,bid) values (#{uid},#{bid})")
    boolean addGzbar(@RequestParam("uid") Integer uid, @RequestParam("bid") Integer bid);

    @Delete("delete from follow where uid = #{uid} and bid = #{bid}")
    boolean delGzbar(@RequestParam("uid") Integer uid, @RequestParam("bid") Integer bid);

    @Update("update users set name=#{name},phone=#{phone},password=#{password},image=#{image},gender=#{gender} where uid=#{uid}")
    void updateUser(@RequestParam("uid") Integer uid, @RequestParam("name") String name, @RequestParam("phone") String phone, @RequestParam("password") String password,
                    @RequestParam("image") String image, @RequestParam("gender") String gender);

    @Update("update user_details set age =#{age},ipaddress=#{ipaddress} where uid=#{uid}")
    void updateUser2(@RequestParam("uid") Integer uid, @RequestParam("age") Integer age, @RequestParam("ipaddress") String ipaddress);

    @Update("update users set image=#{image} where uid= #{uid}")
    boolean updateUser_image(@RequestParam("uid") Integer uid,@RequestParam("image") String image);

    @Update("update user_details set coins = coins + #{coins} where uid = #{uid}")
    void czCoins(@RequestParam("coins") Integer coins, @RequestParam("uid") Integer uid);

    @Select("select * from likes where uid = #{uid} and pid = #{pid} ")
    Likes findLikes(@RequestParam("uid") Integer uid, @RequestParam("pid") Integer pid);

    @Update("update likes set likes =likes + #{likes},shares=shares + #{shares},coins=coins + #{coins} where pid=#{pid} and uid=#{uid}")
    void Likes(@RequestParam("uid") Integer uid, @RequestParam("pid") Integer pid, @RequestParam("likes") Integer likes,
               @RequestParam("shares") Integer shares, @RequestParam("coins") Integer coins);

    @Insert("insert into likes (pid,uid,likes,shares,coins) values (#{pid},#{uid},0,0,0)")
    void addLikes(@RequestParam("uid") Integer uid, @RequestParam("pid") Integer pid);

    @Update("update user_details set coins = coins - #{coins} where uid=#{uid} ")
    void coinsUid(@RequestParam("uid") Integer uid, @RequestParam("coins") Integer coins);
    @Update("update user_details set coins = coins + #{coins} where uid=#{uid} ")
    void coinsUid2(@RequestParam("uid") Integer uid, @RequestParam("coins") Integer coins);

    @Select("select * from bulletins where uid =  #{uid}")
    List<Bulletins> findText(@RequestParam("uid") Integer uid);

    @Insert("insert into bulletins (content,uid) values (#{content},#{uid})")
    void addText(@RequestParam("uid") Integer uid,@RequestParam("content") String content);

    @Select("SELECT \n" +
            "    u.*,d.*,\n" +
            "    (SELECT COUNT(*) FROM follow WHERE uid = u.uid) AS fc,\n" +
            "    IFNULL((SELECT COUNT(*) FROM posts WHERE uid = u.uid),0) as pc \n" +
            "FROM users u join user_details d on u.uid = d.uid \n" +
            "\t\t\t\tJOIN ed e ON d.experience >= e.ex\n" +
            "        AND NOT EXISTS (\n" +
            "            SELECT 1\n" +
            "            FROM ed e2\n" +
            "            WHERE d.experience >= e2.ex AND e2.ex > e.ex\n" +
            "        )\n" +
            "\t\t\t\twhere u.uid = #{uid} ;")
    Users userDetails(@RequestParam("uid") Integer uid);

    @Insert("insert into apply (uid,bid,status) values (#{uid},#{bid},1)")
    void addApply(@RequestParam("uid") Integer uid,@RequestParam("bid") Integer bid);

    @Select("select u.name,a.* from apply a join users u on a.uid = u.uid where a.bid =#{bid} and a.status =1 and a.uid =#{uid}")
    Users findApply(@RequestParam("uid") Integer uid,@RequestParam("bid") Integer bid);

    @Select("select * from users where phone = #{qq}")
    boolean QQlogin(@RequestParam("qq") String qq);

    @Update("update user_details set coins = coins + #{coins} where uid = #{uid}")
    void updateCoins(@RequestParam("uid") Integer uid,@RequestParam("coins") Integer coins);

    @Update("update user_details set experience = experience +10,coins = coins +8 where uid = #{uid}")
    void addEx(Integer uid);
    @Select("select u.*,d.*,e.level,e.levelname from users u join user_details d on u.uid = d.uid join ed  e ON d.experience >= e.ex\n" +
            "        AND\n" +
            "            NOT EXISTS (\n" +
            "                SELECT 1\n" +
            "                FROM ed e2\n" +
            "                WHERE d.experience >= e2.ex AND e2.ex > e.ex\n" +
            "            )\n" +
            "\n" +
            " where name = #{name} ")
    Users findByName(@RequestParam("name") String name);
}